#查找仓库数量
CREATE view irep_sum as
select ownerinfo_login,COUNT(ownerinfo_login) as rep_sum
from irep join NewTable
on irep.ownerinfo_login = NewTable.login
GROUP BY ownerinfo_login
order by ownerinfo_login
#开发者在每个仓库合并pr数量
CREATE view iPA_prsinglemerged_count
as
SELECT repo_name,pull_merged_by_login ,COUNT(repo_name) as ownerinfo_repositories_totalcount
FROM events join NewTable
on events.pull_merged_by_login = NewTable.login
WHERE pull_merged=1
GROUP BY repo_name,pull_merged_by_login
ORDER BY pull_merged_by_login
#仓库里回复issue的数量
CREATE view ownerinfo_repositories_totalcount as
select repo_name,issue_comment_author_login,COUNT(repo_name) as ownerinfo_repositories_totalcount
from events join NewTable nt
on events.issue_comment_author_login = NewTable.login
where issue_comment_author_association in(1,3,5)
GROUP BY repo_name,issue_comment_author_login
order by issue_comment_author_login
#版本更新
SELECT
repo_name,
release_author_login,
COUNT(repo_name) AS i_release_new_sum
FROM github_log.events
INNER JOIN github_log.NewTable ON events.release_author_login = NewTable.login
WHERE isNotNull(release_published_at)
GROUP BY
repo_name,
release_author_login
ORDER BY release_author_login ASC;
#统计分数
CREATE view i_repo_score as
SELECT ownerinfo_login ,SUM(
CASE
WHEN ownerinfo_repositories_totalcount BETWEEN 1 AND 13 THEN 10
WHEN ownerinfo_repositories_totalcount BETWEEN 14 AND 40 THEN 20
WHEN ownerinfo_repositories_totalcount BETWEEN 41 AND 70 THEN 30
WHEN ownerinfo_repositories_totalcount BETWEEN 71 AND 100 THEN 40
WHEN ownerinfo_repositories_totalcount BETWEEN 100 AND 999 THEN 50
WHEN ownerinfo_repositories_totalcount >=1000 THEN 100
ELSE
END
) AS total_score
FROM i_rep_sum irs
GROUP BY ownerinfo_login
ORDER BY total_score DESC
#汇总分数
CREATE view i_all_score2
as
SELECT
  ownerinfo_login,
  ifNull(total_score, 0) AS repo_score,
  ifNull(issue_total_score , 0) AS issue_score,
  ifNull(pr_total_score, 0) AS pr_score,
  ifNull(release_total_score, 0) AS release_score,
  ifNull(total_score, 0)*0.2669 + ifNull(issue_total_score, 0)*0.3802 + ifNull(pr_total_score, 0)*0.24 + ifNull(release_total_score, 0)*0.173 AS total_score_all
FROM
i_repo_score
LEFT JOIN i_issue_score ON i_repo_score.ownerinfo_login  = i_issue_score.issue_comment_author_login
LEFT JOIN i_pr_score ON i_repo_score.ownerinfo_login  = i_pr_score.pull_merged_by_login
LEFT JOIN i_release_score ON i_repo_score.ownerinfo_login  = i_release_score.release_author_login
#赋予等级
CREATE view i_all_score4
as
select ownerinfo_login ,repo_score ,issue_score ,pr_score ,release_score,total_score_all,lev,
case lev
when 'S' then 100
when 'A' then 80
when 'B' then 60
when 'C' then 40
when 'D' then 20
else 0 end as guanli_score_100
from i_all_score3 ias
